﻿Imports System.Data.OleDb

Module XL_TheTinDung
    Structure TheTinDung
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _ngayLap As Date
        Property NgayLap() As Date
            Get
                Return _ngayLap
            End Get
            Set(ByVal value As Date)
                _ngayLap = value
            End Set
        End Property
        Private _taiKhoan As String
        Property TaiKhoan() As String
            Get
                Return _taiKhoan
            End Get
            Set(ByVal value As String)
                _taiKhoan = value
            End Set
        End Property
        Private _nguoiDung As Integer
        Property NguoiDung() As Integer
            Get
                Return _nguoiDung
            End Get
            Set(ByVal value As Integer)
                _nguoiDung = value
            End Set
        End Property
        Private _soTien As Integer
        Property SoTien() As Integer
            Get
                Return _soTien
            End Get
            Set(ByVal value As Integer)
                _soTien = value
            End Set
        End Property
        Private _hanMuc As Integer
        Property HanMuc() As Integer
            Get
                Return _hanMuc
            End Get
            Set(ByVal value As Integer)
                _hanMuc = value
            End Set
        End Property
        Private _laiSuat As Integer
        Property LaiSuat() As Integer
            Get
                Return _laiSuat
            End Get
            Set(ByVal value As Integer)
                _laiSuat = value
            End Set
        End Property
        Private _trangThai As Integer
        Property TrangThai() As Integer
            Get
                Return _trangThai
            End Get
            Set(ByVal value As Integer)
                _trangThai = value
            End Set
        End Property
    End Structure

    Public Function LayDS(ByVal user As Integer) As List(Of TheTinDung)
        Dim ds As New List(Of TheTinDung)
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from TheTinDung where NguoiDung=@NguoiDung and TrangThai=@tt", con)
            cmd.Parameters.AddWithValue("@NguoiDung", user)
            cmd.Parameters.AddWithValue("@tt", 1)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As TheTinDung = New TheTinDung()
                p.ID = rd.GetInt32(0)
                p.NgayLap = rd.GetDateTime(1)
                p.TaiKhoan = rd.GetString(2)
                p.NguoiDung = rd.GetInt32(3)
                p.SoTien = rd.GetInt32(4)
                p.HanMuc = rd.GetInt32(5)
                p.LaiSuat = rd.GetInt32(6)
                p.TrangThai = rd.GetInt32(7)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return ds
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As TheTinDung
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from TheTinDung where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As TheTinDung = New TheTinDung()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.NgayLap = rd.GetDateTime(1)
                p.TaiKhoan = rd.GetString(2)
                p.NguoiDung = rd.GetInt32(3)
                p.SoTien = rd.GetInt32(4)
                p.HanMuc = rd.GetInt32(5)
                p.LaiSuat = rd.GetInt32(6)
                p.TrangThai = rd.GetInt32(7)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Them(ByRef p As TheTinDung) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into TheTinDung(NgayLap, TaiKhoan, NguoiDung, SoTien, HanMuc, LaiSuat,TrangThai) values(@NgayLap, @TaiKhoan, @NguoiDung, @SoTien, @HanMuc, @LaiSuat,@tt)", con)
            cmd.Parameters.AddWithValue("@NgayLap", p.NgayLap)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@NguoiDung", p.NguoiDung)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@HanMuc", p.HanMuc)
            cmd.Parameters.AddWithValue("@LaiSuat", p.LaiSuat)
            cmd.Parameters.AddWithValue("@tt", p.TrangThai)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@IDENTITY"
            p.ID = cmd.ExecuteScalar()
            
            con.Close()
            Return kq
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Function Xoa(ByVal p As TheTinDung) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update TheTinDung set TrangThai=@tt where ID = @ID", con)
            cmd.Parameters.AddWithValue("@tt", 0)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Function CapNhat(ByVal p As TheTinDung) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update TheTinDung set NgayLap=@NgayLap, TaiKhoan=@TaiKhoan, NguoiDung=@NguoiDung, SoTien=@SoTien, HanMuc=@HanMuc, LaiSuat=@LaiSuat where ID = @ID", con)
            cmd.Parameters.AddWithValue("@NgayLap", p.NgayLap)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@NguoiDung", p.NguoiDung)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@HanMuc", p.HanMuc)
            cmd.Parameters.AddWithValue("@LaiSuat", p.LaiSuat)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function KTTen(ByVal ten As String) As Integer
        Try
            Dim kq As Integer = 0
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from TheTinDung where TaiKhoan=@t", con)
            cmd.Parameters.AddWithValue("@t", ten)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As TheTinDung = New TheTinDung()
            If rd.Read() Then
                kq = 1
            End If
            con.Close()
            Return kq
        Catch ex As Exception
            Return 0
        End Try
    End Function

End Module
